﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace Test.MySql.JsonTests.ListAndArray
{
    [TestFixture]
    internal class IListTestsSimple : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }
            [JsonStore(Bucket = "p_ints")]
            public IList<int> p_ints { get; set; }
            [JsonStore(Bucket = "p_strings")]
            public IList<string> p_strings { get; set; }
            [JsonStore(Bucket = "p_bools")]
            public IList<bool> p_bools { get; set; }
        }
        [Table("test2")]
        public class PersonKey
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_ints")]
            public IList<int> p_ints { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_strings")]
            public IList<string> p_strings { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_bools")]
            public IList<bool> p_bools { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("create table test(id int primary key auto_increment,p_ints json,p_strings json,p_bools json)");
            db.ExecuteSql("""
                insert into test(p_ints,p_strings,p_bools) values
                    ('[1,3,2]','["ab","ac","bcd"]','[true,false,true]'),
                    ('[3,4,2]','["ab","acd","bcd"]','[false,false,true]'),
                    ('[1,2,3]','["ab","ac","bcd"]','[false,true,true]');
                """);

            DropTableIfExist("test2");
            db.ExecuteSql("create table test2(id int primary key auto_increment,ext json)");
            db.ExecuteSql("""
                insert into test2(ext) values
                    ('{"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]}'),
                    ('{"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}'),
                    ('{"p_ints":[1,2,3],"p_strings":["ab","ac","bcd"],"p_bools":[false,true,true]}');
                """);
        }

        #region NoKey
        [Test]
        public void TestInsert()
        {
            var insert = db.Insert<Person>().SetEntity(new Person { p_bools = [true, false, true], p_ints = [1, 3, 2], p_strings = ["ab", "ac", "bcd"] });
            var sql = insert.ToSql();
            //sql.ShouldBe("""
            //    insert into test(p_ints,p_strings,p_bools) values('[1,3,2]','["ab","ac","bcd"]','[true,false,true]');
            //    """);
            var p = insert.ExecuteInserted();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":4,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]}""");
        }

        [Test]
        public void TestSelect()
        {
            var select = db.Select<Person>().Where(i => i.p_ints[1] > 2);
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.p_ints,'$') `p_ints`,json_value(t.p_strings,'$') `p_strings`,json_value(t.p_bools,'$') `p_bools`
            //    from test t
            //    where (json_value(json_value(t.p_ints,'$'),'$[1]' returning signed)) > 2;
            //    """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]},{"Id":2,"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}]""");
        }
        [Test]
        public void TestSelect2()
        {
            var select = db.Select<Person>().Where(i => !i.p_bools[1]);
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.p_ints,'$') `p_ints`,json_value(t.p_strings,'$') `p_strings`,json_value(t.p_bools,'$') `p_bools`
            //    from test t
            //    where not (json_value(json_value(t.p_bools,'$'),'$[1]' returning signed));
            //    """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]},{"Id":2,"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}]""");
        }

        [Test]
        public void TestSelect3()
        {
            //应该属于 .Any() 的case吧, 转移出去
            var select = db.Select<Person>().Where(i => i.p_strings.Any());
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.p_ints,'$') `p_ints`,json_value(t.p_strings,'$') `p_strings`,json_value(t.p_bools,'$') `p_bools`
            //    from test t
            //    where json_length(json_value(t.p_strings,'$'))>0;
            //    """);
        }
        [Test]
        public void TestUpdate()
        {
            var update = db.Update<Person>().SetExpr(i => new Person
            {
                Id = 1,
                p_bools = new List<bool> { false, false, true }
            });
            var sql = update.ToSql();
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var p = db.Select<Person>().Where(i => i.Id == 1).FirstOrDefault();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[false,false,true]}""");
        }
        #endregion

        #region Key
        [Test]
        public void TestInsertKey()
        {
            var insert = db.Insert<PersonKey>().SetEntity(new PersonKey { p_bools = [true, false, true], p_ints = [1, 3, 2], p_strings = ["ab", "ac", "bcd"] });
            var sql = insert.ToSql();
            //sql.ShouldBe("""
            //    insert into test2(ext) values(json_object('p_ints',cast('[1,3,2]' as json),'p_strings',cast('["ab","ac","bcd"]' as json),'p_bools',cast('[true,false,true]' as json)));
            //    """);
            var p = insert.ExecuteInserted();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":4,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]}""");
        }

        [Test]
        public void TestSelectKey()
        {
            var select = db.Select<PersonKey>().Where(i => i.p_ints[1] > 2);
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.ext,'$."p_ints"') `p_ints`,json_value(t.ext,'$."p_strings"') `p_strings`,json_value(t.ext,'$."p_bools"') `p_bools`
            //    from test2 t
            //    where (json_value(json_value(t.ext,'$."p_ints"'),'$[1]' returning signed)) > 2;
            //    """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]},{"Id":2,"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}]""");
        }
        [Test]
        public void TestSelect2Key()
        {
            var select = db.Select<PersonKey>().Where(i => !i.p_bools[1]);
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.ext,'$."p_ints"') `p_ints`,json_value(t.ext,'$."p_strings"') `p_strings`,json_value(t.ext,'$."p_bools"') `p_bools`
            //    from test2 t
            //    where not (json_value(json_value(t.ext,'$."p_bools"'),'$[1]' returning signed));
            //    """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]},{"Id":2,"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}]""");
        }

        [Test]
        public void TestSelect3Key()
        {
            //应该属于 .Any() 的case吧, 转移出去
            var select = db.Select<PersonKey>().Where(i => i.p_strings.Any());
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.ext,'$."p_ints"') `p_ints`,json_value(t.ext,'$."p_strings"') `p_strings`,json_value(t.ext,'$."p_bools"') `p_bools`
            //    from test2 t
            //    where json_length(json_value(t.ext,'$."p_strings"'))>0;
            //    """);
        }
        [Test]
        public void TestUpdateKey()
        {
            var update = db.Update<PersonKey>().SetExpr(i => new PersonKey
            {
                Id = 1,
                p_bools = new List<bool> { false, false, true }
            });
            var sql = update.ToSql();
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var p = db.Select<PersonKey>().Where(i => i.Id == 1).FirstOrDefault();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[false,false,true]}""");
        }
        #endregion
    }
}
